﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Web.Mvc;
using System.Data.SqlClient;

namespace BookingManagementSystem.Models
{
    public static class DataAccessLayer
    {
        private enum ColumnsGetBooking
        {
            UniqueId,
            RoomId,
            CustomerId,
            OriginId,
            CheckIn,
            CheckOut
        }

        private enum ColumnsGetCatalogs
        {
            Id,
            Text
        }

        static OleDbConnection GetConnection()
        {
            var dataDir = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
            return new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\db.mdb", dataDir));
        }

        public static List<SelectListItem> GetAllCustomers()
        {
            var customers = new List<SelectListItem>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = "select customer_id, customer_name from t_customer";
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            customers.Add(new SelectListItem { Text = reader.GetString((int)ColumnsGetCatalogs.Text), Value = reader.GetInt32((int)ColumnsGetCatalogs.Id).ToString() });
                        }
                    }
                }
            }
            return customers;
        }

        public static List<SelectListItem> GetAllOrigins()
        {
            var origins = new List<SelectListItem>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = "select origin_id, origin_desc from t_origin";
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            origins.Add(new SelectListItem { Text = reader.GetString((int)ColumnsGetCatalogs.Text), Value = reader.GetInt32((int)ColumnsGetCatalogs.Id).ToString() });
                        }
                    }
                }
            }
            return origins;
        }

        public static List<SelectListItem> GetAllRooms()
        {
            var rooms = new List<SelectListItem>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = "select room_id, room_desc from t_room";
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; 
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            rooms.Add(new SelectListItem { Text = reader.GetString((int)ColumnsGetCatalogs.Text), Value = reader.GetInt32((int)ColumnsGetCatalogs.Id).ToString() });
                        }
                    }
                }
            }
            return rooms;
        }

        public static void AddBooking(BookingModel booking)
        {
            using (OleDbConnection connection = GetConnection())
            {
                connection.Open();
                string command = @"INSERT INTO t_booking
                                (room_id,customer_id,origin_id,booking_checkin,booking_checkout)
                                values
                                (@roomId,@customerId,@originId,@checkIn,@checkOut)";
                using (OleDbCommand com = new OleDbCommand(command, connection))
                {
                    com.Parameters.AddWithValue("@roomId", System.Data.OleDb.OleDbType.Integer).Value = booking.RoomId;
                    com.Parameters.AddWithValue("@customerId", System.Data.OleDb.OleDbType.Integer).Value = booking.CustomerId;
                    com.Parameters.AddWithValue("@originId", System.Data.OleDb.OleDbType.Integer).Value = booking.OriginId;
                    com.Parameters.AddWithValue("@checkIn", System.Data.OleDb.OleDbType.DBDate).Value = booking.CheckInDate;
                    com.Parameters.AddWithValue("@checkOut", System.Data.OleDb.OleDbType.DBDate).Value = booking.CheckOutDate;
                    com.ExecuteNonQuery();
                }
            }
        }

        public static void DeleteBooking(int idBooking)
        {
            using (OleDbConnection con = GetConnection())
            {
                con.Open();
                string command = @"DELETE FROM t_booking
                                    WHERE booking_id = @idBooking";
                using (OleDbCommand com = new OleDbCommand(command, con))
                {
                    com.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = idBooking;
                    com.ExecuteNonQuery();
                }
            }
        }

        public static List<BookingCalendarModel> GetAllBookings()
        {
            var bookings = new List<BookingCalendarModel>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = GetAllBookingsSQLCommand();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingCalendarModel
                            {
                                UniqueId = reader.GetInt32((int)ColumnsGetBookingsByRange.UniqueId),
                                CheckIn = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckIn),
                                CheckOut = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckOut),
                                Description = reader.GetValue((int)ColumnsGetBookingsByRange.Description) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.Description),
                                Color = reader.GetValue((int)ColumnsGetBookingsByRange.RoomColorName) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.RoomColorName)
                            });
                        }
                    }
                }
            }
            return bookings;
        }

        public static BookingModel GetBookingById(int idBooking)
        {
            var bookings = new List<BookingModel>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = "select booking_id,room_id,customer_id,origin_id,booking_checkin,booking_checkout from t_booking where booking_id = @idBooking";
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text; 
                    connection.Open();
                    command.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = idBooking;
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingModel
                            {
                                Id = reader.GetInt32((int)ColumnsGetBooking.UniqueId),
                                CustomerId = reader.GetInt32((int)ColumnsGetBooking.CustomerId),
                                OriginId = reader.GetInt32((int)ColumnsGetBooking.OriginId),
                                RoomId = reader.GetInt32((int)ColumnsGetBooking.RoomId),
                                CheckInDate = reader.GetDateTime((int)ColumnsGetBooking.CheckIn),
                                CheckOutDate = reader.GetDateTime((int)ColumnsGetBooking.CheckOut)
                            });
                        }
                    }
                }
            }
            return bookings[0];
        }

        public static void EditBooking(BookingModel booking)
        {
            using (OleDbConnection connection = GetConnection())
            {
                connection.Open();
                string command = @"UPDATE t_booking
                                 SET room_id = @idRoom,
                                customer_id = @idCustomer,
                                origin_id = @idOrigin,
                                booking_checkin = @checkIn,
                                booking_checkout = @checkOut
                                WHERE booking_id = @idBooking";
                using (OleDbCommand com = new OleDbCommand(command, connection))
                {
                    com.Parameters.AddWithValue("@idRoom", System.Data.OleDb.OleDbType.Integer).Value = booking.RoomId;
                    com.Parameters.AddWithValue("@idCustomer", System.Data.OleDb.OleDbType.Integer).Value = booking.CustomerId;
                    com.Parameters.AddWithValue("@idOrigin", System.Data.OleDb.OleDbType.Integer).Value = booking.OriginId;
                    com.Parameters.AddWithValue("@checkIn", System.Data.OleDb.OleDbType.Date).Value = booking.CheckInDate;
                    com.Parameters.AddWithValue("@checkOut", System.Data.OleDb.OleDbType.Date).Value = booking.CheckOutDate;
                    com.Parameters.AddWithValue("@idBooking", System.Data.OleDb.OleDbType.Integer).Value = booking.Id;
                    com.ExecuteNonQuery();
                }
            }
        }

        private enum ColumnsGetBookingsByRange
        {
            UniqueId,
            CheckIn,
            CheckOut,
            RoomColorName,
            Description

        }

        public static List<BookingCalendarModel> GetBookingsByRange(DateTime from, DateTime to)
        {
            var bookings = new List<BookingCalendarModel>();
            using (OleDbConnection connection = GetConnection())
            {
                string commandString = GetBookingsByRangeSQlCommand();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddWithValue("@StartDate", System.Data.OleDb.OleDbType.Date).Value = from;
                    command.Parameters.AddWithValue("@EndDate", System.Data.OleDb.OleDbType.Date).Value = to;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingCalendarModel()
                            {
                                UniqueId = reader.GetInt32((int)ColumnsGetBookingsByRange.UniqueId),
                                CheckIn = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckIn),
                                CheckOut = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckOut),
                                Description = reader.GetValue((int)ColumnsGetBookingsByRange.Description) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.Description),
                                Color = reader.GetValue((int)ColumnsGetBookingsByRange.RoomColorName) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.RoomColorName),
                            });
                        }
                    }
                }
            }
            return bookings;
        }

        private static string GetBookingsByRangeSQlCommand()
        {
            return @"  select 
                        t_booking.booking_id
                        ,t_booking.booking_checkin
                        ,t_booking.booking_checkout
                        ,t_room.room_color
                        ,t_customer.customer_name + ' '+ t_room.room_desc + ' - Origin:'+ t_origin.origin_desc


                        from 
                        ((t_booking 
                        left join t_room
                        on t_booking.room_id=t_room.room_id)
                        left join t_customer
                        on t_booking.customer_id=t_customer.customer_id)
                        left join t_origin
                        on t_booking.origin_id=t_origin.origin_id
         
                where 

            (((t_booking.booking_checkin >= @StartDate) AND (t_booking.booking_checkin <= @EndDate)) 
            OR ((t_booking.booking_checkout >= @StartDate) AND (t_booking.booking_checkout <= @EndDate))
            OR ((t_booking.booking_checkin < @StartDate) AND (t_booking.booking_checkout > @EndDate)))
                ";
        }

        private static string GetAllBookingsSQLCommand()
        {
            return @"  select 
                        t_booking.booking_id
                        ,t_booking.booking_checkin
                        ,t_booking.booking_checkout
                        ,t_room.room_color
                        ,t_customer.customer_name + ' '+ t_room.room_desc + ' - Origin:'+ t_origin.origin_desc


                        from 
                        ((t_booking 
                        left join t_room
                        on t_booking.room_id=t_room.room_id)
                        left join t_customer
                        on t_booking.customer_id=t_customer.customer_id)
                        left join t_origin
                        on t_booking.origin_id=t_origin.origin_id";
        }
    }
}